﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Weight.Model;
using Dapper;
using Dapper.Contrib;
using System.Data;
using System.Data.SqlClient;
using Weight.Base;
using Dapper.Contrib.Extensions;
using Weight.Model.ParamEntity;
using Weight.Model.ResultEntity;
using Weight.Model.Othter;
using Weight.Base.Utility;
using Weight.Model.TableEntity;
using WeightSystem.Common;

namespace Weight.Dal
{
    public class DalWeight
    {
        /// <summary>
        /// 根据项目编号获取称重数据
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public DataTable GetDataById(string id)
        {
            string sql = "select * from Weight where Id=@Id";
            SqlParameter[] sqlParams = new SqlParameter[]
            {
                new SqlParameter("@Id",id)
            };

            DataSet dataSet = new DataSet();
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(sql, conn as SqlConnection);
                cmd.Parameters.AddRange(sqlParams);
                SqlDataAdapter dp = new SqlDataAdapter(cmd);
                dp.Fill(dataSet);
                return dataSet.Tables[0];
            }
        }

        /// <summary>
        /// 根据项目编号获取称重数据
        /// </summary>
        /// <param name="pronum">项目编号</param>
        /// <returns></returns>
        public DataTable GetDataByPronum(string pronum)
        {
            string sql = "select * from Weight where ProNum=@pronum";
            SqlParameter[] sqlParams = new SqlParameter[]
            {
                new SqlParameter("@pronum",pronum)
            };

            DataSet dataSet = new DataSet();
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(sql, conn as SqlConnection);
                cmd.Parameters.AddRange(sqlParams);
                SqlDataAdapter dp = new SqlDataAdapter(cmd);
                dp.Fill(dataSet);
                return dataSet.Tables[0];
            }
        }

        /// <summary>
        /// 查询称重临时数据
        /// </summary>
        /// <param name="field"></param>
        /// <param name="plageNum"></param>
        /// <returns></returns>
        public DataTable QueryTmpData(string field, string plageNum, string dataSource)
        {
            string sql = " select Id as DataId,  ProNum as ProjectNum," + field + "  from  Weight where  WeightState=1 and IsDel=0";
            if (!string.IsNullOrEmpty(plageNum))
            {
                sql += " and PlateNum like '%" + plageNum + "%'";
            }

            if (!string.IsNullOrEmpty(dataSource))
            {
                sql += " and DataSource= '" + dataSource + "'";
            }
            sql += " order by WeightDate desc ,WeightTime desc ";


            DataTable dt = new DataTable();
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                SqlDataAdapter dp = new SqlDataAdapter(sql, conn as SqlConnection);
                dp.Fill(dt);
                return dt;
            }
        }

        /// <summary>
        /// 根据sql获取数据
        /// </summary>
        /// <param name="sql">sql</param>
        /// <returns></returns>
        public DataSet GetWeightDataBySql(string sql)
        {
            DataSet dataSet = new DataSet();
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                SqlDataAdapter dp = new SqlDataAdapter(sql, conn as SqlConnection);
                dp.Fill(dataSet);
                return dataSet;
            }
        }

        /// <summary>
        /// 称重查询
        /// </summary>
        public DataSet WeightQueryData(ReqQueryWeightReq reqParam)
        {
            if (reqParam != null)
            {
                ///获取过滤条件
                string field = string.Empty;
                if (!string.IsNullOrEmpty(reqParam.Field))
                {
                    field = " Id," + reqParam.Field;
                }
                else
                {
                    field = " * ";
                }
                StringBuilder filterBuider = new StringBuilder(" where IsDel=0 ");
                string startTime = reqParam.StartTime;
                string endTime = reqParam.EndTime;
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    filterBuider.Append(" and  Convert(datetime,WeightDate+' '+WeightTime) >=Convert(datetime,'").Append(startTime).Append("')");
                    filterBuider.Append(" and  Convert(datetime,WeightDate+' '+WeightTime) <=Convert(datetime,'").Append(endTime).Append("')");
                }

                if (reqParam.WeightState == 1)
                {
                    filterBuider.Append(" and WeightState=1 ");
                }
                else
                {
                    filterBuider.Append(" and WeightState=2 ");
                }

                if (!string.IsNullOrEmpty(reqParam.Pronum))
                {
                    filterBuider.Append(" and pronum like '%").Append(reqParam.Pronum).Append("%'");
                }

                if (reqParam.OtherFilter != null && !string.IsNullOrEmpty(reqParam.OtherFilter))
                {
                    filterBuider.Append(" and (").Append(reqParam.OtherFilter).Append(")");
                }

                //排序
                string orderBy = " order by WeightDate desc,WeightTime desc ";

                int rowStart = reqParam.PageSize * (reqParam.PageIndex - 1);
                int rowEnd = reqParam.PageSize * reqParam.PageIndex;
                StringBuilder sqlBuider = new StringBuilder();
                if (reqParam.IsPage)
                {
                    sqlBuider.Append("  select  ").Append(field)
                    .Append(" from  (select * ,ROW_NUMBER()over(" + orderBy + ") as rank  from Weight " + filterBuider + " ) as tab  ")
                    .Append("where rank between " + (rowStart + 1) + " and " + rowEnd + "");
                }
                else
                {
                    sqlBuider.Append(" select ").Append(field).Append(" from  Weight ").Append(filterBuider).Append(orderBy);
                }

                sqlBuider.Append("; select count(1) as [count] from  Weight  ").Append(filterBuider.ToString());
                if (!string.IsNullOrEmpty(reqParam.SumField))
                {
                    sqlBuider.Append(";select " + reqParam.SumField + " from Weight").Append(filterBuider);
                }

                string sql = sqlBuider.ToString();
                using (IDbConnection conn = SessionFactory.CreateConnection())
                {
                    DataSet dataSet = new DataSet();
                    SqlDataAdapter dp = new SqlDataAdapter(sql, conn as SqlConnection);
                    dp.Fill(dataSet);
                    return dataSet;
                }
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 称重统计
        /// </summary>
        public DataSet WeightSummaryData(SummaryWeightReq reqParam)
        {
            if (reqParam != null)
            {
                ///获取过滤条件
                string field = string.Empty;
                if (string.IsNullOrEmpty(reqParam.Field))
                {
                    return null;
                }

                StringBuilder filterBuider = new StringBuilder(" where IsDel=0 ");
                string startTime = reqParam.StartTime;
                string endTime = reqParam.EndTime;
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    filterBuider.Append(" and  Convert(datetime,WeightDate+' '+WeightTime) >=Convert(datetime,'").Append(startTime).Append("')");
                    filterBuider.Append(" and  Convert(datetime,WeightDate+' '+WeightTime) <=Convert(datetime,'").Append(endTime).Append("')");
                }

                if (reqParam.WeightState == 1)
                {
                    filterBuider.Append(" and WeightState=1 ");
                }
                else
                {
                    filterBuider.Append(" and WeightState=2 ");
                }

                if (!string.IsNullOrEmpty(reqParam.PlateNum))
                {
                    filterBuider.Append(" and PlateNum='").Append(reqParam.PlateNum).Append("'");
                }


                if (reqParam.OtherFilter != null && !string.IsNullOrEmpty(reqParam.OtherFilter))
                {
                    filterBuider.Append(" and (").Append(reqParam.OtherFilter).Append(")");
                }

                string groupBy = " group by " + reqParam.GroupField;
                StringBuilder sqlBuider = new StringBuilder();
                sqlBuider.Append(" select ").Append(reqParam.Field).Append(" from Weight").Append(filterBuider.ToString())
                    .Append(" ").Append(groupBy);
                if (!string.IsNullOrEmpty(reqParam.sumFieldBuider))
                {
                    sqlBuider.Append(";select " + reqParam.sumFieldBuider + " from Weight").Append(filterBuider);
                }

                string sql = sqlBuider.ToString();
                using (IDbConnection conn = SessionFactory.CreateConnection())
                {
                    DataSet dataSet = new DataSet();
                    SqlDataAdapter dp = new SqlDataAdapter(sql, conn as SqlConnection);
                    dp.Fill(dataSet);
                    return dataSet;
                }
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取导出数据
        /// </summary>
        public DataSet GetQueryExportData(ReqQueryWeightReq reqParam)
        {
            if (reqParam != null)
            {
                ///获取过滤条件
                string field = string.Empty;
                if (!string.IsNullOrEmpty(reqParam.Field))
                {
                    field = " Id," + reqParam.Field;
                }
                else
                {
                    field = " * ";
                }
                StringBuilder filterBuider = new StringBuilder(" where IsDel=0 ");
                string startTime = reqParam.StartTime;
                string endTime = reqParam.EndTime;
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    filterBuider.Append(" and  Convert(datetime,WeightDate+' '+WeightTime) >=Convert(datetime,'").Append(startTime).Append("')");
                    filterBuider.Append(" and  Convert(datetime,WeightDate+' '+WeightTime) <=Convert(datetime,'").Append(endTime).Append("')");
                }

                if (reqParam.WeightState == 1)
                {
                    filterBuider.Append(" and WeightState=1 ");
                }
                else
                {
                    filterBuider.Append(" and WeightState=2 ");
                }

                if (!string.IsNullOrEmpty(reqParam.Pronum))
                {
                    filterBuider.Append(" and pronum like '%").Append(reqParam.Pronum).Append("%'");
                }

                if (reqParam.OtherFilter != null && !string.IsNullOrEmpty(reqParam.OtherFilter))
                {
                    filterBuider.Append(" and (").Append(reqParam.OtherFilter).Append(")");
                }

                //排序
                string orderBy = " order by WeightDate desc,WeightTime desc ";

                StringBuilder sqlBuider = new StringBuilder();
                sqlBuider.Append("  select  ").Append(field)
                    .Append(" from   Weight " + filterBuider)
                    .Append(orderBy);

                if (!string.IsNullOrEmpty(reqParam.SumField))
                {
                    sqlBuider.Append(";select " + reqParam.SumField + " from Weight").Append(filterBuider);
                }

                string sql = sqlBuider.ToString();
                using (IDbConnection conn = SessionFactory.CreateConnection())
                {
                    DataSet dataSet = new DataSet();
                    SqlDataAdapter dp = new SqlDataAdapter(sql, conn as SqlConnection);
                    dp.Fill(dataSet);
                    return dataSet;
                }
            }
            else
            {
                return null;
            }
        }




        /// <summary>
        /// 查询字段值
        /// </summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public IEnumerable<string> QueryFieldVal(string field)
        {
            string sql = " select distinct " + field + " from  Weight where  " + field + " is not null ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<string>(sql);
            }
        }

        /// <summary>
        /// 查询录入的称重数据
        /// </summary>
        /// <param name="field"></param>
        /// <param name="plageNum"></param>
        /// <returns></returns>
        public DataTable QueryWeightData(string field, string dataSource)
        {
            string sql = " select Id as DataId,  ProNum as ProjectNum," + field + "  from  Weight where  IsDel=0";

            if (!string.IsNullOrEmpty(dataSource))
            {
                sql += " and DataFrom= '" + dataSource + "'";
            }
            sql += " order by WeightDate desc ,WeightTime desc ";


            DataTable dt = new DataTable();
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                SqlDataAdapter dp = new SqlDataAdapter(sql, conn as SqlConnection);
                dp.Fill(dt);
                return dt;
            }
        }

        /// <summary>
        /// 根据Id获取称重数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public WeightEntity GetWeightById(string id, int state)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select *  from  Weight where Id=@id ";
                if (state > 0)
                {
                    sql += " and WeightState=@state";
                }
                return conn.Query<WeightEntity>(sql, new { id, state }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 根据卡号获取称重数据
        /// </summary>
        /// <param name="cardNum"></param>
        /// <returns></returns>
        public WeightEntity GetWeightByCardNum(string cardNum, int state)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select *  from  Weight where CardNum=@cardNum ";
                if (state > 0)
                {
                    sql += " and WeightState=@state";
                }
                return conn.Query<WeightEntity>(sql, new { cardNum, state }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 根据车号获取称重数据
        /// </summary>
        /// <param name="plateNum"></param>
        /// <returns></returns>
        public WeightEntity GetWeightByPlateNum(string plateNum, int state)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select *  from  Weight where PlateNum=@plateNum ";
                if (state > 0)
                {
                    sql += " and WeightState=@state";
                }
                return conn.Query<WeightEntity>(sql, new { plateNum, state }).FirstOrDefault();
            }
        }


        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Add(WeightEntity entity)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                long res = conn.Insert(entity);
                return true;
            }
        }

        /// <summary>
        /// 添加称重数据同步其他业务数据(订单、派车、发卡)
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool AddByTran(WeightEntity entity, WeightDataFrom dataFrom, bool isConsume = false)
        {
            bool result = false;
            using (var connSession = SessionFactory.CreateTransSession())
            {
                try
                {
                    connSession.Connection.Insert(entity, connSession.Transaction);

                    // 添加消费记录
                    if ((string.IsNullOrEmpty(entity.ContractNo) && entity.Net > 0 && dataFrom == WeightDataFrom.Ticket) || isConsume)
                    {
                        connSession.Connection.Insert(new ContractTicketConsumeEntity()
                        {
                            Id = new GetIdHelper().GetDateTimeAndRandId("CN"),
                            MainId = entity.ContractMainNo,
                            TicketId = entity.ContractNo,
                            PlateNum = entity.PlateNum,
                            Material = entity.Material,
                            Type = entity.BusinessType,
                            ConsumeVal = entity.Net ?? 0,
                            ConsumeMoney = entity.Amount ?? 0,
                            Consumer = entity.Customer,
                            ConsumeTime = DateTime.Now.ToString(),
                            Price = entity.Price ?? 0,
                            Mode = entity.ContractModel,
                            Status = 1,
                            IsDel = false,
                            UpdateFlag = 0,
                            UploadFlag = 0
                        }, connSession.Transaction);
                    }

                    // 更新派车状态
                    if (!string.IsNullOrEmpty(entity.DisPatchNo) && dataFrom == WeightDataFrom.Dispatch)
                    {
                        int state = 1;
                        connSession.Connection.Execute("update  DispatchCar set State=@state  where Id=@id", new { state, id = entity.DisPatchNo }, connSession.Transaction);
                    }

                    // 更新发卡状态
                    if (!string.IsNullOrEmpty(entity.CardNo) && dataFrom == WeightDataFrom.SendCard)
                    {
                        int state = entity.Net > 0 ? 3 : 1;
                        connSession.Connection.Execute("update  CardManager set State=@state  where Id=@id", new { state, id = entity.CardNo }, connSession.Transaction);
                    }

                    connSession.Commit();
                    result = true;

                }
                catch (Exception ex)
                {
                    connSession.RollBack();
                    result = false;
                }

                return result;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update(WeightEntity entity)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Update(entity);
            }
        }

        /// <summary>
        /// 修改称重数据同步其他业务数据(订单、派车、发卡)
        /// </summary>
        /// <param name="entity">数据实体 </param>
        /// <param name="isConsume">是否消费订单</param>
        /// <returns>操作结果</returns>
        public bool UpdateByTran(WeightEntity entity, WeightDataFrom dataFrom,bool isConsume=false)
        {
            bool result = false;
            using (var connSession = SessionFactory.CreateTransSession())
            {
                try
                {
                    var res = connSession.Connection.Update(entity, connSession.Transaction);
                    if (res)
                    {
                        // 添加消费记录
                        if ((!string.IsNullOrEmpty(entity.ContractNo) && entity.Net > 0 && dataFrom == WeightDataFrom.Ticket) || isConsume)
                        {
                            connSession.Connection.Insert(new ContractTicketConsumeEntity()
                            {
                                Id = new GetIdHelper().GetDateTimeAndRandId("CN"),
                                MainId = entity.ContractMainNo,
                                TicketId = entity.ContractNo,
                                PlateNum = entity.PlateNum,
                                Material = entity.Material,
                                Type = entity.BusinessType,
                                ConsumeVal = entity.Net ?? 0,
                                ConsumeMoney = entity.Amount ?? 0,
                                Consumer = entity.Customer,
                                ConsumeTime = DateTime.Now.ToString(),
                                Price = entity.Price ?? 0,
                                Mode = entity.ContractModel,
                                Status = 1,
                                IsDel = false,
                                UpdateFlag = 0,
                                UploadFlag = 0
                            }, connSession.Transaction);
                        }

                        // 更新派车状态
                        if (!string.IsNullOrEmpty(entity.DisPatchNo) && dataFrom == WeightDataFrom.Dispatch)
                        {
                            int state = entity.Net > 0 ? 3 : 2;
                            connSession.Connection.Execute("update  DispatchCar set State=@state  where Id=@id", new { state, id = entity.DisPatchNo }, connSession.Transaction);
                        }

                        // 更新发卡状态
                        if (!string.IsNullOrEmpty(entity.CardNo) && dataFrom == WeightDataFrom.SendCard)
                        {
                            int state = entity.Net > 0 ? 3 : 1;
                            connSession.Connection.Execute("update  CardManager set State=@state  where Id=@id", new { state, id = entity.CardNo }, connSession.Transaction);
                        }

                        connSession.Commit();
                        result = true;
                    }
                    else
                    {
                        connSession.RollBack();
                    }
                }
                catch (Exception ex)
                {
                    connSession.RollBack();
                    result = false;
                }

                return result;
            }
        }

        /// <summary>
        /// 判断数据是否已经存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsExistById(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select count(1) from  Weight where Id=@id";
                int count = conn.ExecuteScalar<int>(sql, new { id });
                return count > 0 ? true : false;
            }
        }

        /// <summary>
        /// 判断车号对应状态的数据是否已经存在
        /// </summary>
        /// <param name="plateNum">车号</param>
        /// <param name="state">状态</param>
        /// <returns></returns>
        public bool IsExistByPlateNum(string plateNum, int state)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select count(1) from  Weight where PlateNum=@plateNum and  WeightState=@state and IsDel =@isDel";
                int count = conn.ExecuteScalar<int>(sql, new { plateNum, state, isDel = false });
                return count > 0 ? true : false;
            }
        }

        /// <summary>
        /// 获取车号对应的称重数据
        /// </summary>
        /// <param name="plateNum">车号</param>
        /// <param name="state">状态</param>
        /// <returns></returns>
        public WeightEntity GetByPlateNum(string plateNum, int state)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select *  from  Weight where PlateNum=@plateNum and  WeightState=@state and IsDel =@isDel";
                return  conn.Query<WeightEntity>(sql, new { plateNum, state, isDel = false })?.FirstOrDefault();
            }
        }

        /// <summary>
        /// 获取车号对应的称重数据
        /// </summary>
        /// <param name="plateNum">车号</param>
        /// <param name="state">状态</param>
        /// <returns></returns>
        public WeightEntity GetByPlateNum(string plateNum)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select *  from  Weight where PlateNum=@plateNum and IsDel =@isDel order by  WeightDate desc,WeightTime desc ";
                return conn.Query<WeightEntity>(sql, new { plateNum, isDel = false })?.FirstOrDefault();
            }
        }

        /// <summary>
        /// 指定卡号的一次过磅数据是否存在
        /// </summary>
        /// <param name="CardNo"></param>
        /// <returns></returns>
        public bool IsExistOnceWeightByCardId(string CardNum)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select count(1) from  Weight where CardNum=@CardNum and  WeightState=@state and IsDel =@isDel";
                int count = conn.ExecuteScalar<int>(sql, new { CardNum, state = 1, isDel = false });
                return count > 0 ? true : false;
            }
        }

        public string GetMaxPronum(string ScaleNo)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select max(ProNum)  from  Weight where ScaleNo=@ScaleNo ";
                string proNum = conn.Query<string>(sql, new { ScaleNo }).FirstOrDefault();
                return proNum;
            }
        }

        /// <summary>
        /// 判断车号对应状态的数据是否已经存在
        /// </summary>
        /// <param name="plateNum">车号</param>
        /// <param name="state">状态</param>
        /// <returns></returns>
        public bool IsExistById(string id, int state)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select count(1) from  Weight where Id=@id and  WeightState=@state and IsDel =@isDel";
                int count = conn.ExecuteScalar<int>(sql, new { id, state, isDel = false });
                return count > 0 ? true : false;
            }
        }

        /// <summary>
        ///通过Id删除无效数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelById(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                int count = conn.Execute("delete from Weight where Id=@id", new { id });
                return count > 0 ? true : false;
            }
        }



        /// <summary>
        /// 创建触发器
        /// </summary>
        /// <returns></returns>
        public bool CreateTriggerForPronum()
        {
            string sql = string.Empty;
            try
            {
                StringBuilder sqlBuider = new StringBuilder(" create trigger  WegithTrigger").Append(" on  Weight")
                   .Append(" after insert  as    declare @id nvarchar(50),@maxCode nvarchar(50),@ScaleNum nvarchar(50) ,@OldDate nvarchar(50),@NewDate nvarchar(50) ")
                   .Append(" begin ")
                   .Append(" if exists(select 1 from   inserted)  and not exists(select 1 from deleted) ")
                     .Append("begin")
                        .Append(" set @id=(select id from inserted )")
                        .Append(" set @ScaleNum=(select ScaleNo from  inserted )")
                        .Append(" set @maxCode=(select  substring(max(ProNum),11,4) from  Weight  where ScaleNo=@ScaleNum  and WeightDate=CONVERT(nvarchar(50), GETDATE(),23))")
                        .Append(" if @maxCode is null ")
                            .Append(" set @maxCode =CONVERT(nvarchar(50), GETDATE(),112)+@ScaleNum+'0001'")
                         .Append(" else ")
                            .Append(" begin ")
                                 .Append(" set @maxCode=Convert(int,@maxCode)+1 ")
                                 .Append(" set @maxCode= CONVERT(nvarchar(50), GETDATE(),112)+@ScaleNum+right(replicate('0',4)+@maxCode,4)  ")
                            .Append(" end ")
                            .Append(" update Weight set  ProNum=@maxCode where id=@id")
                     .Append(" end")
                   .Append(" end");
                sql = sqlBuider.ToString();
                using (IDbConnection conn = SessionFactory.CreateConnection())
                {
                    conn.Execute(sql);
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 判断触发器是否存在
        /// </summary>
        /// <returns></returns>
        public bool WeightTriggerIsExist()
        {
            string sql = " select count(1) from dbo.sysobjects where id = object_id(N'[dbo].[WegithTrigger]') and OBJECTPROPERTY(id, N'IsTrigger')=1 ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                int count = conn.ExecuteScalar<int>(sql);
                return count > 0 ? true : false;
            }
        }


        /// <summary>
        ///作废称重数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool SetWeightDel(string id)
        {
            string sql = "update  Weight set IsDel=1 where Id=@Id";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Execute(sql, new { Id = id }) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 获取完成一次过磅的车辆信息
        /// </summary>
        /// <returns></returns>
        public int GetOnceWeightCount()
        {
            string sql = "select count(1) from Weight where  WeightState=@state and IsDel =@isDel  and IsConfirm=@isConfirm ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { state = 1, isDel = false, isConfirm = false });
            }
        }

        /// <summary>
        /// 获取完成过磅数量
        /// </summary>
        /// <returns></returns>
        public int GetOverWeightCount()
        {
            string sql = "select count(1) from Weight where  WeightState=@state and IsDel =@isDel ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { state = 2, isDel = false });
            }
        }

        /// <summary>
        /// 获取装卸货的数量
        /// </summary>
        /// <returns></returns>
        public int GetConfirmCount()
        {
            string sql = "select count(1) from Weight where  WeightState=@state and IsDel =@isDel  and IsConfirm=@isConfirm ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { state = 1, isDel = false, isConfirm = true });
            }
        }

        /// <summary>
        /// 获取条形图数据
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MaterialPieChartRes> GetMaterialNetCollect()
        {
            string sql = "select material as name ,sum(Net) as value  from weight where  WeightState=@state and IsDel =@isDel  group by material ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<MaterialPieChartRes>(sql, new { state = 2, isDel = false });
            }
        }

        /// <summary>
        /// 获取饼形图数据
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MaterialPieChartRes> GetMaterialCountCollect()
        {
            string sql = "select material as name ,sum(1) as value  from weight where  WeightState=@state and IsDel =@isDel  group by material ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<MaterialPieChartRes>(sql, new { state = 2, isDel = false });
            }
        }

        /// <summary>
        /// 获取线形图数据
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MaterialPieChartRes> GetMonthWeightCountCollect(string startDate, string endDate)
        {
            string sql = "select WeightDate as name ,sum(1) as value  from weight where  WeightState=@state and IsDel =@isDel  ";
            sql += " and Convert(DateTime,WeightDate) >=Convert(DateTime,@startDate) and  Convert(DateTime,WeightDate) <=Convert(DateTime,@endDate)  ";
            sql += "group by WeightDate ";
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<MaterialPieChartRes>(sql, new { state = 2, isDel = false, startDate, endDate });
            }
        }

    }
}
